package com.sky.mapper;

import com.sky.dto.GoodsSalesDTO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.time.LocalDateTime;
import java.util.List;

@Mapper
public interface ReportMapper {
    /**
     * 统计间断时间内的营业额
     * @param begin
     * @param end
     * @return
     */
    @Select("select sum(amount) from orders where status = 5 and order_time between #{begin} and #{end}")
    Double queryTurnover(LocalDateTime begin, LocalDateTime end);

    /**
     * 统计截止到当前日期总用户量
     * @param date
     * @return
     */
    @Select("select count(1) from user where create_time < #{data}")
    Integer queryTotalUser(LocalDateTime date);


    /**
     * 统计当前日期内新注册的用户
     * @param beginTime
     * @param endTime
     * @return
     */
    @Select("select count(*) from user where create_time between #{beginTime} and #{endTime}")
    Integer queryNewUser(LocalDateTime beginTime, LocalDateTime endTime);

    /**
     * 查询今天内所有订单
     * @param beginTime
     * @param endTime
     * @return
     */
    @Select("select count(*) from orders where order_time between #{beginTime} and #{endTime}")
    Integer queryAllOrder(LocalDateTime beginTime, LocalDateTime endTime);

    /**
     * 查询今天内有效订单(状态为已完成)
     * @param beginTime
     * @param endTime
     * @return
     */
    @Select("select count(*) from orders where status = 5 and order_time between #{beginTime} and #{endTime}")
    Integer queryValidOrder(LocalDateTime beginTime, LocalDateTime endTime);

    /**
     * 多表联合查询,查询销量排行榜
     * @param begin
     * @param end
     * @return
     */
    @Select("select ord.name,sum(ord.number) number from orders o left join order_detail  ord on o.id = ord.order_id\n" +
            "where o.order_time between #{begin} and #{end} and o.status = 5 \n" +
            "group by ord.name\n" +
            "order by number desc,name\n" +
            "limit 0,10")
    List<GoodsSalesDTO> queryTop(LocalDateTime begin, LocalDateTime end);

    /**
     * 根据订单状态查询当天订单数
     * @return
     */
    @Select("select count(1) from orders where order_time between #{begin} and #{end} and status = #{status}")
    Integer findOrdersCountByStatus(Integer status,LocalDateTime begin,LocalDateTime end);
}



























